#!/bin/bash
APP=edu

# 如果输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

dws_process_user_chapter_1d="
insert overwrite table ${APP}.dws_process_user_chapter_1d partition(dt='$do_date')
select
	chapter_id,
	chapter_name,
    count(o2.video_id) video_count_1d,
    sum(play_sec) video_sec_sum_1d,
    count(distinct user_id) video_user_count_1d
    

from (
    select
		chapter_id,
		chapter_name,
		video_id,dt
	from ${APP}.dim_course_full
	where dt='$do_date' and chapter_id is not null
) o2
left join
(
	select
		user_id,
		play_sec,
		video_id
	from ${APP}.dwd_video_play_inc

) o3 on o2.video_id = o3.video_id
where user_id is not null and play_sec is not null
group by chapter_id, chapter_name,dt;
"

dws_trade_user_cart_add_1d="
insert overwrite table ${APP}.dws_trade_user_cart_add_1d partition(dt='$do_date')
select
    user_id,
    count(*),
    sum(course_id)
from ${APP}.dwd_trade_cart_add_inc
where dt='$do_date'
group by user_id;
"
dws_trade_user_order_1d="
insert overwrite table ${APP}.dws_trade_user_order_1d partition(dt='$do_date')
select
    user_id,
    count(distinct (order_id)),
       sum(1),
       sum(origin_amount),
       sum(nvl(coupon_reduce, 0)),
       sum(final_amount)      
from ${APP}.dwd_trade_order_detail_inc
where dt='$do_date'
group by user_id;
"

dws_trade_user_payment_1d="
insert overwrite table ${APP}.dws_trade_user_payment_1d partition(dt='$do_date')
select
    user_id,
    count(distinct(order_id)),
    sum(distinct(user_id)),
    sum(split_payment_amount)
from ${APP}.dwd_trade_pay_detail_inc
where dt='$do_date'
group by user_id;

"
dws_traffic_page_visitor_page_view_1d="
insert overwrite table ${APP}.dws_traffic_page_visitor_page_view_1d partition(dt='$do_date')
select
    mid_id,
    brand,
    model,
    operate_system,
    page_id,
    sum(during_time),
    count(*)
from ${APP}.dwd_traffic_page_view_inc
where dt='$do_date'
group by mid_id,brand,model,operate_system,page_id;
"
dws_traffic_session_page_view_1d="
insert overwrite table ${APP}.dws_traffic_session_page_view_1d partition(dt='$do_date')
select
    session_id,
    mid_id,
    brand,
    model,
    operate_system,
    version_code,
    channel,
    sum(during_time),
    count(*)
from ${APP}.dwd_traffic_page_view_inc
where dt='$do_date'
group by session_id,mid_id,brand,model,operate_system,version_code,channel;
"
dws_process_user_course_1d="
insert overwrite table ${APP}.dws_process_user_course_1d partition(dt='$do_date')
select
	id course_id,
	course_name,
    count(o2.video_id) video_count_1d,
    sum(play_sec) video_sec_sum_1d,
    count(distinct user_id) video_user_count_1d
    

from (
    select
		id,
		course_name,
		video_id,dt
	from ${APP}.dim_course_full
	where dt='$do_date' and id is not null

) o2
left join
(
	select
		user_id,
		play_sec,
		video_id
	from ${APP}.dwd_traffic_video_play_inc
) o3 on o2.video_id = o3.video_id
where user_id is not null and play_sec is not null
group by id, course_name;
"
dws_trade_province_order_1d="
insert overwrite table ${APP}.dws_trade_province_order_1d partition(dt='$do_date')
select
    province_id,
    p.name province_name,
    region_id,
   area_code,
    iso_code,
iso_3166_2,
    order_count_1d,
    order_original_amount_1d,
    activity_reduce_amount_1d,
    order_total_amount_1d
   
from
(
    select
        province_id,
        count(distinct(order_id)) order_count_1d,
        sum(origin_amount) order_original_amount_1d,
        sum(nvl(coupon_reduce,0)) activity_reduce_amount_1d,
        sum(final_amount) order_total_amount_1d
        
    from ${APP}.dwd_trade_order_detail_inc
    group by province_id
)o
left join
(
    select
        id,
        name,
        region_id,
area_code,
        iso_code,
iso_3166_2
    from ${APP}.dim_province_full
    where dt='$do_date'
)p
on o.province_id=p.id;
"

case $1 in
    "dws_trade_province_order_1d" )
        hive -e "$dws_trade_province_order_1d"
    ;;
    "dws_trade_user_cart_add_1d" )
        hive -e "$dws_trade_user_cart_add_1d"

    ;;
    "dws_process_user_course_1d" )
        hive -e "$dws_process_user_course_1d"
        
    ;;
    "dws_process_user_chapter_1d" )
        hive -e "$dws_process_user_chapter_1d"
        
    ;;
    "dws_trade_user_order_1d" )
        hive -e "$dws_trade_user_order_1d"
    ;;
    
    "dws_trade_user_payment_1d" )
        hive -e "$dws_trade_user_payment_1d"
    ;;
    
    "dws_traffic_page_visitor_page_view_1d" )
        hive -e "$dws_traffic_page_visitor_page_view_1d"
    ;;
    "dws_traffic_session_page_view_1d" )
        hive -e "$dws_traffic_session_page_view_1d"
    ;;
    "all" )
        hive -e "$dws_process_user_course_1d$dws_process_user_chapter_1d$dws_trade_province_order_1d$dws_trade_user_cart_add_1d$dws_trade_user_order_1d$dws_trade_user_payment_1d$dws_traffic_page_visitor_page_view_1d$dws_traffic_session_page_view_1d"
    ;;
esac

